package cn.modoumama.excelUilt.util;

import java.io.File;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.modoumama.excelUilt.common.Common;
import cn.modoumama.excelUilt.po.SheetEntity;

public class ReadExcel {
    public static Logger logger = LoggerFactory.getLogger(ReadExcel.class);
    private Workbook workbook;

    public ReadExcel() {
        super();
    }

    public ReadExcel(String path) {
        super();
        try {
            init(path);
        } catch (Exception e) {
            logger.error("初始化读取文件失败！", e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取单元格的内容
     *
     * @param sheet           sheet 页
     * @param listCombineCell 存放合并单元格的list
     * @param cell            需要判断的单元格
     * @return
     */
    public static Cell getCombineCell(Sheet sheet, List<CellRangeAddress> listCombineCell, Cell cell) {
        int firstC, lastC, firstR, lastR;
        for (CellRangeAddress ca : listCombineCell) {
            // 获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) {
                if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR) {
                    Row fRow = sheet.getRow(firstR);
                    cell = fRow.getCell(firstC);
                }
            }
        }
        return cell;
    }

    /**
     * 读取文件，并解析
     *
     * @param path
     */
    public void init(String path) {
        path = path == null ? Common.EMPTY : path.trim();
        if (!Common.EMPTY.equals(path)) {
            String postfix = Common.getPostfix(path);
            if (!Common.EMPTY.equals(postfix) && (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix) || Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix))) {
                try {
                    workbook = WorkbookFactory.create(new File(path));
                } catch (Exception e) {
                    logger.error("文件夹读取失败", e);
                    throw new RuntimeException(e);
                }
            } else {
                logger.error("无效路径,或类型不正确");
                throw new RuntimeException("无效路径,或类型不正确");
            }
        } else {
            try {
                throw new RuntimeException("请传入文件路径");
            } catch (Exception e) {
                logger.error("文件名为空,请传入文件路径", e);
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 获取集合用String[]接收
     * List&lt;List&lt;String[]>> excelData = readExcel.readExcel();<br/>
     * 		for (List&lt;String[]> excelSheet : excelData) {<br/>
     * 			for (String[] row : excelSheet) {<br/>
     * 				for (int i = 0, max = row.length-1; i &lt;= max; i++) {<br/>
     * 					if( i < max){
     * 						System.out.print(row[i]+"\t");<br/>
     * 					}else{
     * 						System.out.print(row[i]+"\r\n");<br/>
     * 					}<br/>
     * 				}<br/>
     * 			}<br/>
     * 		}<br/>
     */
    public List<List<String[]>> readExcel() {
        List<List<String[]>> excelSheets = new ArrayList<List<String[]>>();
        List<CellRangeAddress> cellRanges;
        List<String[]> excelRows;
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet != null) {
                cellRanges = getCombineCell(sheet);
                excelRows = new ArrayList<String[]>();
                // 读取行
                for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row != null) {
                        int cellNum = row.getLastCellNum();
                        String[] excelCells = new String[cellNum];
                        for (int i = 0; i < cellNum; i++) {
                            try {
                                excelCells[i] = getValue(String.class, getCombineCell(sheet, cellRanges, row.getCell(i)));
                            } catch (Exception e) {
                                logger.error("获取第" + (rowNum + 1) + "行，第" + (i + 1) + "列数据失败", e);
                                throw new RuntimeException(e);
                            }
                        }
                        excelRows.add(excelCells);
                    }
                }
                excelSheets.add(excelRows);
            }
        }
        return excelSheets;
    }
    
    /**
     * 自带表头，表头为属性名<br/>
     * @param clazz<br/>
		List&lt;SteelModel&gt; steels = readExcel.readFieldHeadExcel(SteelModel.class).get(0);
     * @return
     */
    public <T> List<List<T>> readFieldHeadExcel(Class<T> clazz){
    	return readExcel(clazz);
    }
    
    
    /**
     * 获取集合并封装起来
     *
     * @param clazz  需要封装的类型<br/>
     * @param fields 每一列对象的需要封装的对象成员属性<br/>
       	SheetEneity fields = new SheetEneity();<br/>
		fields.put(0, "id");<br/>
		fields.put(1, "name");<br/>
		List&lt;SteelModel&gt; steels = readExcel.readNotHeadExcel(SteelModel.class, fields).get(0);<br/>
     */
    public <T> List<List<T>>  readNotHeadExcel(Class<T> clazz, SheetEntity fields){
    	if(fields == null){
    		throw new RuntimeException("实体类对应关系（fields）为空！");
    	}
    	if(fields.isHead()){
    		throw new RuntimeException("实体类对应关系（fields）中不能包含描述信息！");
    	}
    	return readExcel(clazz, fields);
    }
    
    /**
     * 自带表头，表头为描述
     * @param clazz<br/>
     * @param fieldMaps<br/>
     	Map&lt;String, String&gt; fieldMaps = new HashMap&lt;&gt;();
		fieldMaps.put("名称", "name");
		fieldMaps.put("日期", "date");
		fieldMaps.put("编号", "id");
		List&lt;SteelModel&gt; steels = readExcel.readDescHradExcel(SteelModel.class, fieldMaps).get(0);
     * @return
     */
    public <T> List<List<T>> readDescHradExcel(Class<T> clazz, Map<String, String> fieldMaps){
    	if(fieldMaps == null){
    		throw new RuntimeException("实体类对应关系（fieldMaps）不能为空！");
    	}
    	return readExcel(clazz, fieldMaps);
    }


   
    private <T> List<List<T>> readExcel(Class<T> clazz, Map<String, String> fieldMaps) {
    	SheetEntity fields = new SheetEntity();
    	 Sheet sheet = workbook.getSheetAt(0);
    	 Row row = sheet.getRow(0);
    	 for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
    		 Cell cell = row.getCell(i);
    		 String desc = cell.getStringCellValue();
    		 if(desc != null){
    			 desc = desc.trim();
    		 }
    		 if(StringUtils.isNotBlank(desc)){
    			String field = fieldMaps.get(desc);
    			if(StringUtils.isNotBlank(field)){
    				fields.put(i, field, desc);
    			}
    		 }
    		 
    	 }
    	return readExcel(clazz, fields);
    }
   
    private <T> List<List<T>> readExcel(Class<T> clazz, SheetEntity fields) {
        List<List<T>> excelSheets = new ArrayList<List<T>>();
        List<CellRangeAddress> cellRanges;
        List<T> excelRows;
        //读取页
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet != null) {
                cellRanges = getCombineCell(sheet);
                excelRows = new ArrayList<T>();
                // 读取行
                int rowNum = 0;
                if(fields.isHead()){
                	rowNum = 1;
                }
                
                for (; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row != null) {
                        //根据class反射生产实体类
                        T excelCells = null;
                        try {
                            excelCells = clazz.newInstance();
                        } catch (Exception e) {
                            logger.error("实例化" + clazz + "失败", e);
                        }
                        for (Integer i : fields.keySet()) {
                        	  //把值注入到实体内中
                            try {
                                Field field = clazz.getDeclaredField(fields.getField(i));
                                field.setAccessible(true);
                                field.set(excelCells, getValue(field.getType(), getCombineCell(sheet, cellRanges, row.getCell(i))));
                            } catch (NoSuchFieldException e) {
                                logger.error("在" + clazz + "中找不到成员变量" + fields.get(i), e);
                                throw new RuntimeException(e);
                            } catch (Exception e) {
                            	e.printStackTrace();
                                throw new RuntimeException(e);
                            }
						}
                        excelRows.add(excelCells);
                    }
                }
                excelSheets.add(excelRows);
            }
        }
        return excelSheets;
    }
    
   
    private <T> List<List<T>> readExcel(Class<T> clazz) {
    	
        List<List<T>> excelSheets = new ArrayList<List<T>>();
        List<CellRangeAddress> cellRanges;
        List<T> excelRows;
        //读取页
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet != null) {
                cellRanges = getCombineCell(sheet);
                excelRows = new ArrayList<T>();
                
                /**
            	 * 获取表头信息
            	 */
                Row head = sheet.getRow(0);
            	SheetEntity fields = new SheetEntity();
            	 if (head != null) {
	            	 // 读取表头
	                for (int cellNum = 0; cellNum < head.getLastCellNum(); cellNum++) {
	                	String field = null;
						try {
							field = getValue(String.class, getCombineCell(sheet, cellRanges, head.getCell(cellNum)));
							if(StringUtils.isNotBlank(field)){
			                	fields.put(cellNum, field,"");
							}
						} catch (Exception e) {
							e.printStackTrace();
						}
	                }
            	 }
                
                // 读取行
                for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row != null) {
                        //根据class反射生产实体类
                        T excelCells = null;
                        try {
                            excelCells = clazz.newInstance();
                        } catch (Exception e) {
                            logger.error("实例化" + clazz + "失败", e);
                        }
                        for (Integer i : fields.keySet()) {
                        	  //把值注入到实体内中
                            try {
                                Field field = clazz.getDeclaredField(fields.getField(i));
                                field.setAccessible(true);
                                field.set(excelCells, getValue(field.getType(), getCombineCell(sheet, cellRanges, row.getCell(i))));
                            } catch (NoSuchFieldException e) {
                                logger.error("在" + clazz + "中找不到成员变量" + fields.get(i), e);
                                throw new RuntimeException(e);
                            } catch (Exception e) {
                            	e.printStackTrace();
                                throw new RuntimeException(e);
                            }
						}
                        excelRows.add(excelCells);
                    }
                }
                excelSheets.add(excelRows);
            }
        }
        return excelSheets;
    }

    /**
     * 合并单元格处理--加入list
     *
     * @param sheet
     * @return
     */
    public List<CellRangeAddress> getCombineCell(Sheet sheet) {
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
        // 获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        // 遍历合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            // 获得合并单元格加入list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    /**
     * 把单元格的内容转换成指定类型
     *
     * @param clazz 需要转换成的类型
     * @param cell  单元格
     * @return
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     * @throws SecurityException
     */
    @SuppressWarnings("all")
    private <T> T getValue(Class<T> clazz, Cell cell) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException {
        if (null != cell) {
            String value = "";
            DecimalFormat df = new DecimalFormat("#######.####");
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getRichStringCellValue().getString().trim();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        return (T) Common.parsDate(clazz, date);
                    } else {
                        value = df.format(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue()).trim();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    value = cell.getCellFormula();
                    break;
            }
            return Common.parsValue(clazz, value);
        } else {
            return null;
        }
    }

}
